package com.isoft.mapper;

import com.isoft.entity.DfmeAirlEntity;
import org.apache.ibatis.annotations.*;

@Mapper
public interface DfmeAirlMapper {
    @Insert("INSERT INTO MSG_META(SENDER, RECEIVER, SEQUENCE_NUMBER, DATE_TIME, MSG_TYPE, SUB_TYPE) " +
            "VALUES(#{meta.sender,jdbcType=VARCHAR}, #{meta.receiver,jdbcType=VARCHAR}, " +
            "#{meta.sequenceNumber,jdbcType=NUMERIC}, TO_TIMESTAMP(#{meta.dateTime,jdbcType=VARCHAR}, 'YYYYMMDDHH24MISS'), " +
            "#{meta.type,jdbcType=VARCHAR}, #{meta.subType,jdbcType=VARCHAR})")
    @Options(useGeneratedKeys = true, keyProperty = "meta.msgId", keyColumn = "MSG_ID")
    void insertMeta(DfmeAirlEntity entity);

    @Insert("MERGE INTO FLIGHT_BASE USING (SELECT #{dflt.flightId} AS FLIGHT_ID FROM DUAL) S ON (FLIGHT_BASE.FLIGHT_ID = S.FLIGHT_ID) " +
            "WHEN MATCHED THEN UPDATE SET " +
            "FLIGHT_FILE_ID = #{dflt.flightFileId,jdbcType=VARCHAR}, " +
            "FLIGHT_IDENTIFIER = #{dflt.flightIdentifier,jdbcType=VARCHAR}, " +
            "FLIGHT_ATTRIBUTE = #{dflt.flightAttribute,jdbcType=VARCHAR} " +
            "WHEN NOT MATCHED THEN INSERT (" +
            "FLIGHT_ID, FLIGHT_FILE_ID, FLIGHT_IDENTIFIER, FLIGHT_ATTRIBUTE) " +
            "VALUES (" +
            "#{dflt.flightId,jdbcType=NUMERIC}, " +
            "#{dflt.flightFileId,jdbcType=VARCHAR}, " +
            "#{dflt.flightIdentifier,jdbcType=VARCHAR}, " +
            "#{dflt.flightAttribute,jdbcType=VARCHAR})")
    void insertFlightBase(DfmeAirlEntity entity);

    @Insert({
            "<script>",
            "INSERT INTO FLIGHT_ROUTE(ROUTE_ID, FLIGHT_ID, AIRPORT_CODE, AIRPORT_SEQ, ",
            "SCHEDULED_TIME, ESTIMATED_TIME, ACTUAL_TIME, AIRPORT_TERMINAL) VALUES ",
            "<foreach collection='dflt.airl.arpts' item='arpt' separator=','>",
            "(#{arpt.airportSeq}, #{dflt.flightId}, ",
            "#{arpt.airportCode,jdbcType=VARCHAR}, #{arpt.airportSeq}, ",
            "CASE WHEN #{arpt.scheduledTime,jdbcType=VARCHAR} IS NOT NULL AND #{arpt.scheduledTime} != '' THEN TO_TIMESTAMP(#{arpt.scheduledTime}, 'YYYYMMDDHH24MISS') ELSE NULL END, ",
            "CASE WHEN #{arpt.estimatedTime,jdbcType=VARCHAR} IS NOT NULL AND #{arpt.estimatedTime} != '' THEN TO_TIMESTAMP(#{arpt.estimatedTime}, 'YYYYMMDDHH24MISS') ELSE NULL END, ",
            "CASE WHEN #{arpt.actualTime,jdbcType=VARCHAR} IS NOT NULL AND #{arpt.actualTime} != '' THEN TO_TIMESTAMP(#{arpt.actualTime}, 'YYYYMMDDHH24MISS') ELSE NULL END, ",
            "#{arpt.airportTerminal,jdbcType=VARCHAR})",
            "</foreach>",
            "</script>"
    })
    void insertFlightRoute(DfmeAirlEntity entity);

    @Select("SELECT COUNT(1) FROM AIRPORT WHERE AIRPORT_CODE = #{airportCode,jdbcType=VARCHAR}")
    int checkAirportExists(@Param("airportCode") String airportCode);

    @Insert("INSERT INTO AIRPORT(AIRPORT_CODE, AIRPORT_TERMINAL) " +
            "VALUES(#{airportCode,jdbcType=VARCHAR}, #{airportTerminal,jdbcType=VARCHAR})")
    void insertAirport(
            @Param("airportCode") String airportCode,
            @Param("airportTerminal") String airportTerminal);

    @Insert("INSERT INTO FLIGHT_IRREGULARITY(" +
            "FLIGHT_ID, ABNORMAL_STATUS, ABNORMAL_REASON, " +
            "INTERNAL_STATUS, INTERNAL_REASON, MANUAL_ABNORMAL_STATUS, MANUAL_ABNORMAL_REASON, " +
            "MANUAL_INTERNAL_STATUS, MANUAL_INTERNAL_REASON) " +
            "VALUES(" +
            "#{dflt.flightId,jdbcType=NUMERIC}, " +
            "'AIRL', " +
            "NULL, " +
            "'AIRL', " +
            "NULL, " +
            "NULL, NULL, NULL, NULL)")
    int insertIrregularity(DfmeAirlEntity entity);
}